﻿/*******************************************************************************
 * Copyright © 2017 FriendStar.Framework 版权所有
 * Author: FZH
 * Description: FriendStar快速开发平台
 * Website：http://www.FriendStar.cn
*********************************************************************************/
using System.Collections.Generic;
using FriendStar.Code;
using System.Data.SqlClient;
using FriendStar.Application.VBaseData;
using System.Text;
using System.Data;
using System;


namespace FriendStar.Application.BaseData
{
    /// <summary>
	/// 设备档案对应的数据应用功能类。
	/// </summary>
	public class EquipmentArchiveApp
    {
        public object GetEquipmentArchivies(Pagination pagination, string equipNO, string equipModel, string equipName, string equipUnit, string equipWorkshop, string manuUnit,string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" SELECT
                        ISNULL(MaintainStatus, 'c') + ',' + EquipID MaintainStatusID,
                          case MaintainStatus when 0 then '编辑'  when 2 then '冻结' when 1 then '发布' else '未知' end MaintainStatusName,
	                        MaintainStatus,
	                        EquipID,
	                        EquipUnit,
	                        EquipUnitName,
	                        EquipWorkshop,
	                        EquipWorkshopName,
	                        EquipNO,
	                        EquipName,
	                        EquipModel,
	                        JFDF,
	                        EquipLine,
	                        EquipLineName,
	                        EquipType,
	                        EquipTypeName,
	                        PositionNumber,
	                        AssetNumber,
	                        ManuUnit,
	                        ManuUnitName,
	                        EquNumber,
	                        manufactNo,
	                        EnableDate,
	                        EquValue,
	                        DepreYears,
	                        UseSituation,
	                        EffectSituation,
	                        MoldExplain,
	                        CY_EngPressure,
	                        CY_EngPreTrip,
	                        CY_MaxCloseHei,
	                        CY_MaxLoadModeHei,
	                        CY_LoadMoleReguHei,
	                        CY_PlateThick,
	                        CY_SlideTripNum_Space,
	                        CY_SlideTripNum,
	                        CY_SlideTrip,
	                        CY_TableSize,
	                        CY_SlideBotSize,
	                        CY_HitbarsNum,
	                        CY_HitbarTrip,
	                        CY_AirCushNum,
	                        CY_AirCushTrip,
	                        CY_AirCushPressure,
	                        CY_EngWeight,
	                        CY_TopRodHoleSize,
	                        CY_SubElecSum,
	                        CY_SubElecPower,
	                        JQ_PlateWidth,
	                        JQ_PlateThick,
	                        KJ_PlateWidth,
	                        KJ_PlateThick,
	                        KJ_MStrength,
	                        KJ_Inradium,
	                        KJ_Extadium,
	                        KJ_KJJLoadWeight,
	                        KJ_SJJLoadWeight,
	                        KJ_LengthRange,
	                        QX_MThick,
	                        QX_MWidth,
	                        QX_MLength,
	                        QX_LPMaxWeight,
	                        QX_LDMaxWeight,
	                        QX_OilFilm,
	                        QX_FiltAccu,
	                        QX_CleanAccuracy,
                            TC_XCZDLJ,
	                        TC_XCBDLJ,
	                        TC_ZQSQZL,
	                        TC_FQSQZL,
	                        TC_ZQSZDSQGD,
	                        TC_FQSZDSQGD,
	                        TC_ZQSJTZJ,
	                        TC_FQSJTZJ,
	                        TC_DCZDLJ,
	                        DHL_DCZDLJ,
	                        DHL_DCBDLJ,
	                        DHL_QSQZL,
	                        DHL_ZDQSGD,
                            InputUser,
	                        InputDate,
	                        InputTime,
	                        FAuditingBy,
	                        FAuditingDate,
	                        FAuditingTime
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_Equ_11_DA_CEquipInfo";


            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(equipNO))
            {
                conn.Append(string.Format(" and equipNO = '{0}'", equipNO));
            }

            if (!string.IsNullOrEmpty(equipModel))
            {
                conn.Append(string.Format(" and equipModel = '{0}'", equipModel));
            }

            if (!string.IsNullOrEmpty(equipName))
            {
                conn.Append(string.Format(" and equipName = '{0}'", equipName));
            }

            if (!string.IsNullOrEmpty(equipUnit))
            {
                conn.Append(string.Format(" and equipUnit = '{0}'", equipUnit));
            }

            if (!string.IsNullOrEmpty(equipWorkshop))
            {
                conn.Append(string.Format(" and equipWorkshop = '{0}'", equipWorkshop));
            }

            if (!string.IsNullOrEmpty(manuUnit))
            {
                conn.Append(string.Format(" and manuUnit = '{0}'", manuUnit));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL( pagination.sidx,pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }


            return result;
        }

        public object ChangeEquipmentArchive(string type, string ids, string curUser, DateTime now)
        {
            int result = 0;

            List<string> list = null;

            string sql = string.Empty;

            string[] strs = null;

            sql = @"update Equ_11_DA_CEquipInfo set 
                        MaintainStatus = @MaintainStatus,
                        FAuditingBy = @FAuditingBy,
                        FAuditingDate = @FAuditingDate,
                        FAuditingTime = @FAuditingTime
            where EquipID in ({0})";

            strs = ids.Split(',');

            list = new List<string>();

            foreach (string item in strs)
            {
                list.Add(string.Format("'{0}'", item));
            }

            sql = string.Format(sql, string.Join(",", list.ToArray()));

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@MaintainStatus", type),
                new SqlParameter("@FAuditingBy", curUser),
                new SqlParameter("@FAuditingDate", now.ToString("yyyy-MM-dd")),
                new SqlParameter("@FAuditingTime", now.ToString("HH:mm:ss"))
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object UpdateEquipmentArchive(Dictionary<string, object> item)
        {
            int result = 0, count = item.Count;

            string sql = @"update Equ_11_DA_CEquipInfo set 
            {0}
            where EquipID =@EquipID";

            List<string> conn = null, columns = null;

            SqlParameter[] pars = null;

            string moldid = item["EquipID"].ToString();

            DateTime now = DateTime.Now;

            item.Remove("EquipID");

            pars = new SqlParameter[count];

            conn = new List<string>();

            columns = new List<string>();

            foreach (string key in item.Keys)
            {
                conn.Add(string.Format("{0} = @{0}\r\n", key));

                columns.Add(key);
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()));

            for (int i = 0; i < item.Keys.Count; i++)
            {
                pars[i] = new SqlParameter(string.Format("@{0}", columns[i]), item[columns[i]]);
            }

            pars[item.Count] = new SqlParameter("@EquipID", moldid);

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object InsertEquipmentArchive(Dictionary<string, object> item)
        {
            int result = 0;

            string sql = @"insert into  Equ_11_DA_CEquipInfo ({0}) values({1})";

            List<string> conn = null, conn2 = null, columns = null;

            SqlParameter[] pars = null;

            pars = new SqlParameter[item.Count];

            conn = new List<string>();

            conn2 = new List<string>();

            columns = new List<string>();

            foreach (string key in item.Keys)
            {
                conn.Add(string.Format("{0}", key));

                conn2.Add(string.Format("@{0}", key));

                columns.Add(key);
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()), string.Join(",", conn2.ToArray()));

            for (int i = 0; i < item.Keys.Count; i++)
            {
                pars[i] = new SqlParameter(string.Format("@{0}", columns[i]), item[columns[i]]);
            }

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetUseUnitData(string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            Dictionary<string, object> item = null;

            string sql = @"select MakeLineNO,MakeLineShortName,MakeLineNO+' '+MakeLineShortName MakeLineNOMakeLineShortName  from LXK_16_LX_MakeLine where UseUnit =@UseUnit  order by MakeLineNO";
            
            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetEquipmentArchivie(string id)
        {
            Dictionary<string, object> result = null;


            return result;
        }

        public object DeleteEquipmentArchive(string id)
        {
            int result = 0;

            string sql = @"delete from Equ_11_DA_CEquipInfo where EquipID = @EquipID";

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@EquipID", id)
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetEquipmentArchive(string id)
        {
            Dictionary<string, object> result = null;

            string sql = @" SELECT
                        EquipID,
	                    EquipUnit,
	                    EquipNO,
	                    EquipModel,
	                    EquipLine,
	                    EquipType,
	                    ManuUnit,
	                    manufactNo,
	                    EquValue,
	                    UseSituation,
	                    EquipWorkshop,
	                    EquipName,
	                    JFDF,
	                    PositionNumber,
	                    AssetNumber,
	                    EnableDate,
	                    EffectSituation,
	                    EquNumber,
	                    DepreYears,
	                    KJ_PlateWidth,
	                    KJ_MStrength,
	                    KJ_Inradium,
	                    KJ_KJJLoadWeight,
	                    KJ_PlateThick,
	                    KJ_LengthRange,
	                    KJ_Extadium,
	                    KJ_SJJLoadWeight,
	                    JQ_PlateWidth,
	                    JQ_PlateThick,
	                    QX_Mthick,
	                    QX_Mlength,
	                    QX_LPMaxWeight,
	                    QX_LDMaxWeight,
	                    QX_Mwidth,
	                    QX_OilFilm,
	                    QX_FiltAccu,
	                    QX_CleanAccuracy,
	                    CY_EngPressure,
	                    CY_MaxCloseHei,
	                    CY_LoadMoleReguHei,
	                    CY_SlideTripNum_Space,
	                    CY_SlideTrip,
	                    CY_SlideBotSize,
	                    CY_HitbarTrip,
	                    CY_AirCushTrip,
	                    CY_EngWeight,
	                    CY_SubElecSum,
	                    CY_EngPreTrip,
	                    CY_MaxLoadModeHei,
	                    CY_PlateThick,
	                    CY_SlideTripNum,
	                    CY_TableSize,
	                    CY_HitbarsNum,
	                    CY_AirCushNum,
	                    CY_AirCushPressure,
	                    CY_TopRodHoleSize,
	                    CY_SubElecPower,
	                    TC_XCZDLJ,
	                    TC_ZQSQZL,
	                    TC_ZQSZDSQGD,
	                    TC_ZQSJTZJ,
	                    TC_DCZDLJ,
	                    DHL_DCZDLJ,
	                    DHL_QSQZL,
	                    TC_XCBDLJ,
	                    TC_FQSQZL,
	                    TC_FQSZDSQGD,
	                    TC_FQSJTZJ,
	                    TC_DCBDLJ,
	                    DHL_DCBDLJ,
	                    DHL_ZDQSGD,
                        MoldExplain
                    FROM
	                    Equ_11_DA_CEquipInfo
                    WHERE
	                    EquipID = @EquipID";

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@EquipID", id)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {
                    result = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }
    }
}




